#!bin/bash
#/*************Head Section**************************************************************************/
#/*Script Use: 对数据开放平台中间结果进一步优化，减少人力时间，提高开发愉悦度                       */
#/*Create Date: 2021-12-16 09:07:00                                                                 */
#/*Script Developed By: BRACE                                                                       */
#/*Script Developed Date:                                                                 			*/
#/*Script Checked By:                                                                               */
#/*Script Checked Date:                                                                             */

#/*************Revision History**********************************************************************/
#Date Revised    Revised by     Revision Note
#--------------- -------------- --------------------------------------------------------------------
#2021/12/16      BRACE            CREATE
#/***************************************************************************************************/
#/*Script File: find_gdm_tablename
                         
#脚本权限控制
[ `whoami` == "root" ] && echo "请使用非root用户操作" && exit 245

#导入系统函数库
. /etc/init.d/functions
. lib/functions

#定义变量
sql_filename=$1
td_tablename=''
gdm_tablename=''
dn=prd_tbd_gdm
tn=''
#涉及到的字段数量
filed_nums=0;
#组个数
group_nums=0;
#开始匹配 O(n) 之后可以进行算法优化
# sql_filename -> TD作业 -> GDM表名
for i in `cat lib/td_hive.txt | awk -F" " '{print $1}'` 
do
	#echo $i
	echo "$sql_filename" | grep -i $i >/dev/null
	if [ $? -eq 0 ];then
		td_tablename=$i
		gdm_tablename=`cat lib/td_hive.txt | grep "$td_tablename" | awk -F" " '{print $5}'`
	fi
done

#得到td_tablename gdm_tablename
echo 
printMsgInRed "================= res ================="

printMsgInRed "sql_filename:$sql_filename"
printMsgInRed "td_tablename:$td_tablename"
printMsgInRed "gdm_tablename:$gdm_tablename"

#gdm表名小写转换
gdm_tablename=`echo $gdm_tablename | tr 'A-Z' 'a-z'`
tn=$gdm_tablename
printMsgInRed "gdm_tablename:$gdm_tablename"

#初始化工程目录
#mkdir -p res/$gdm_tablename
#rm -f res/$gdm_tablename/*
#echo "${gdm_tablename} | $td_tablename" >> res/td_2_gdm
#sql转码
iconv -fgb2312 -tutf-8 $sql_filename -o res/$gdm_tablename/${gdm_tablename}_utf

echo 
echo_success
#创建表工作空间
printMsgInRed "得到gdm表名并初始化工程目录"

#得到组个数
group_nums=`cat res/$gdm_tablename/${gdm_tablename}_utf | grep -E "^/\*Group" | wc -l`

echo 
echo_success
#得到组个数
printMsgInRed "得到组个数"
printf "\n\t组数量 : ${group_nums}\n"

echo 
echo_success
#与hive交互，得到字段名和注释
#printMsgInRed "开始连接hive，得到字段名和注释"
printMsgInRed "开始连接hive，查看字段注释"

#先找DB_ID
DB_ID_sql="SELECT DB_ID FROM DBS WHERE NAME='"${dn}"';"
DB_ID=$(connMysqlAndOneResColunm "${DB_ID_sql}")
#printMsgInRed "====== $tn ===========DB_ID: $DB_ID==============="
#拿到 TBLS 对应的表 TBL_ID
TBL_ID_sql="SELECT TBL_ID FROM TBLS WHERE DB_ID='"$DB_ID"' and TBL_NAME='"${tn}"';"
TBL_ID=$(connMysqlAndOneResColunm "${TBL_ID_sql}")
#printMsgInRed "====== $tn ===========TBL_ID: $TBL_ID==============="
#拿到 TBLS 对应的表 SD_ID 
SD_ID_sql="SELECT SD_ID FROM TBLS WHERE DB_ID='"$DB_ID"' and TBL_ID='"${TBL_ID}"';"
SD_ID=$(connMysqlAndOneResColunm "${SD_ID_sql}")
#printMsgInRed "====== $tn ===========SD_ID: $SD_ID==============="
#拿到 TBLS 对应的表 CD_ID 
CD_ID_sql="select CD_ID from SDS where SD_ID ='"${SD_ID}"';"
CD_ID=$(connMysqlAndOneResColunm "${CD_ID_sql}")
#printMsgInRed "====== $tn ===========CD_ID: $CD_ID==============="
#再拿到location URL
files_url_path_sql="select LOCATION as hive_url from SDS where SD_ID ='"$SD_ID"';"
file_hdfs_url=$(connMysqlAndOneResColunm "${files_url_path_sql}")
#printMsgInRed "====== $tn ===========LOCATION URL==============="
#printMsgInRed "$file_hdfs_url"

PARTITION_KEYS_sql="SELECT PKEY_NAME FROM PARTITION_KEYS WHERE TBL_ID='"${TBL_ID}"';"
partition_keys=$(connMysqlAndMoreResColunm "${PARTITION_KEYS_sql}")
partition_keys=$(echo $partition_keys | awk -F" " '{print $2}')
#echo "$dbname 分区字段为：$partition_keys"

#printMsgInRed "$dbname 有以下${PARTITION_NUMS}个分区"
PARTITION_NUMS_sql="SELECT PART_NAME FROM PARTITIONS WHERE TBL_ID='"${TBL_ID}"';"
PARTITION_NUMS=$(connMysqlAndMoreResColunm "${PARTITION_NUMS_sql}")
#printMsgInRed "$PARTITION_NUMS"


#字段数量
fileds_cnt_sql="select count(1) from COLUMNS_V2 where CD_ID = '"${CD_ID}"' "
filed_nums=$(connMysqlAndOneResColunm "${fileds_cnt_sql}")
let filed_nums++
printf "\n\t字段数量 : ${filed_nums}\n"
#printMsgInRed "====== $tn ===========CD_ID: $CD_ID==============="
fileds_list_sql="select COLUMN_NAME,COMMENT from COLUMNS_V2 where CD_ID = '"${CD_ID}"' order by INTEGER_IDX;"
fileds_list=$(connMysqlAndMoreResColunmNoH "${fileds_list_sql}")
#printMsgInRed "\n$fileds_list"
#保存表字段及注释
echo "$fileds_list" > res/$gdm_tablename/${gdm_tablename}_fileds_list
echo
#手动添加etl作业名字段
echo "etl_job ETL加载作业名" >> res/$gdm_tablename/${gdm_tablename}_fileds_list

				for filed in `cat res/$gdm_tablename/${gdm_tablename}_fileds_list | awk -F" " '{print $2}'`
				do
						printf "\t${filed}\n"						
				done
echo 
echo_success

printMsgInRed "保存表字段和注释，手动添加etl作业名字段"
#printMsgInRed "\r\n${gdm_tablename}涉及${filed_nums}个字段"
#给转码后的sql文件打行号
sh lib/filerow.sh res/$gdm_tablename/${gdm_tablename}_utf > res/$gdm_tablename/${gdm_tablename}_utf_rowline

echo 
echo_success

#printMsgInRed "给转码后的sql文件打行号"
printMsgInRed "处理转码后的sql文件"

#进行第一次范围确定
#cat res/$gdm_tablename/${gdm_tablename}_utf_rowline | grep -E "/\*Group|INSERT INTO \S*I\b|SELECT|FROM|[0-9][0-9].;" > res/$gdm_tablename/${gdm_tablename}_utf_rowline_rgex

#cat res/$gdm_tablename/${gdm_tablename}_utf_rowline | grep -E "^[0-9][0-9].*/\*Group[0-9]|LOCKING TABLE" > res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups
#优化 LOCKING TABLE 多空格现象
cat res/$gdm_tablename/${gdm_tablename}_utf_rowline | grep -E "^[0-9][0-9].*/\*Group[0-9]|LOCKING.*TABLE" > res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups
#grep -E "^[0-9][0-9].*/\*Group[0-9]|^[0-9]{2,3}.;$" 这个还需要再做一步处理
echo 
echo_success
printMsgInRed "进行第一次范围确定\r\n"

				for filed in `cat res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups`
				do
						#printf "\t${filed}\n"
						:
				done
				
echo 
echo_success
printMsgInRed "进行第二次范围确定\r\n"

cat res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups | awk -F" " '{print $1}' > res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups_range

#cat res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups_range

#打印 group range index
#定义范围数组 

index=0 #刚好组数和行索引差一
				for filed in `cat res/$gdm_tablename/${gdm_tablename}_utf_rowline_groups_range`
				do
						#printf "\t${filed}\n"
						groups_range[$index]=$filed
						let index++
				done

#printf "\tindex:$index\n"
#printf "\tgroups_range:[ ${groups_range[*]} ]\n"
				
#开始切割sql源文档
echo 
echo_success
printMsgInRed "开始切割td_sql源文档\r\n"
				
#开始定义变量
#${group_nums}
#行数
row=1
#group 数组索引
head=0
tail=1	
# groups_range:[ 97 125 155 ]
#复杂度O(n)
				
while read line 
do	
	#echo $row 
	#echo $head $tail ${groups_range[$head]} ${groups_range[$tail]}			
	
	if [[ "$row" -ge "${groups_range[$head]}" ]] && [[ "$row" -le "${groups_range[$tail]}" ]];then
		#echo $row 
	    #echo $head $tail ${groups_range[$head]} ${groups_range[$tail]}
		#echo "$line"
		#printf 	"${line}\n"
		echo "$line" >> res/$gdm_tablename/${gdm_tablename}_groups_${head}
	fi
	

	#控制索引组移动	 且 行数大于尾索引的数组值 首尾索引滑动一次
	if  [[ "$row" -gt "${groups_range[$tail]}" ]];then
		#printMsgInRed "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
		let head++ 
		let tail++
		#printMsgInRed "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
	fi
	
	#当尾索引小于组数
	if [[ $tail -gt $group_nums ]];then
			break
	fi
	
	#行数游标,不停止
	let row++	
done < res/$gdm_tablename/${gdm_tablename}_utf	
				
					
#分组完成
echo 
echo_success
printMsgInRed "td_sql源文档 分组完成\r\n"

#开始进行gdm和odm字段匹配，为excel行对齐做准备
echo 
echo_success
printMsgInRed "开始进行gdm和odm字段匹配，为excel行对齐做准备\r\n"


#遍历group组，进行字段匹配
printf "\n\t${gdm_tablename}  ----  ${td_tablename}  filed mapping \n" >> res/$gdm_tablename/${gdm_tablename}_${td_tablename}_res
printf "\n\t字段数量 : ${filed_nums}\n" >> res/$gdm_tablename/${gdm_tablename}_${td_tablename}_res
for((num=0;num<${group_nums};num++))
do

	filed_num_index=1
	while read line
	do
		#得到字段名及字段注释
		gdm_filed_name=`echo $line | awk -F" " '{print $1}'`
		gdm_filed_comment=`echo $line | awk -F" " '{print $2}'`
		
		#echo "$gdm_filed_name" "$gdm_filed_comment"
		
		#通过gdm注释去找td字段
		td_comment="$gdm_filed_comment"
		td_filed_name=`cat res/$gdm_tablename/${gdm_tablename}_groups_${num} | grep -E "/\*$td_comment" | awk -F" " '{print $1}' `
		#去掉逗号
		td_filed_name=`echo ${td_filed_name/,/}`
		
		#echo $td_filed_name
		
		#通过td的字段得到odm的行
		odm_filed_name=`cat res/$gdm_tablename/${gdm_tablename}_groups_${num} | grep -E "/\*$td_filed_name" | awk -F"/*" '{print $1}' `
		odm_filed_name=`echo ${odm_filed_name/,/}`
		
		printf "${num}\t ${filed_num_index}\t ${gdm_filed_name}\t ${gdm_filed_comment}\t  ${td_filed_name}\t ${odm_filed_name}\n"
		
		printf "${num}\t ${filed_num_index}\t ${gdm_filed_name}\t ${gdm_filed_comment}\t ${odm_filed_name}\n" >> res/$gdm_tablename/${gdm_tablename}_${td_tablename}_res
		
		let filed_num_index++
		
	done < res/$gdm_tablename/${gdm_tablename}_fileds_list
	
done

echo 
echo_success
printMsgInRed "gdm和odm字段匹配完成\r\n"

cat 
